<?php

namespace App\Controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Cake\Datasource\ConnectionManager;

class UsersController extends AppController
{

  public $_page_num = 30;

  public function initialize()
  {
    parent::initialize();
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.06.05
  * ps：导出用户积分
  * */
  public function getUserPoints($month = null)
  {
    $month = empty($month) ? date('m') : $month;
    $this->loadComponent('SysconfigCache');
    $sys = $this->SysconfigCache->getVal('expert');
    $this->loadModel('CategoryContents');
    $category_content = $this->CategoryContents->find()
      ->select(['content_id'])
      ->where(['category_id' => $sys])
      ->toArray();
    $where['user_id not in'] = empty($category_content) ? [0] : array_column($category_content, 'content_id');
    $where['month'] = $month;
    $this->loadModel('UserPoints');
    $user_points = $this->UserPoints->find()
      ->where($where)
      ->toArray();
    if (empty($user_points)) {
      $this->G->error("data_null");
      return null;
    }
    $users = $this->Users->find()
      ->where(['id in' => array_column($user_points, 'user_id')])
      ->toArray();
    $user = [];
    foreach ($users as $k => $v) {
      $user[$v['id']] = $v['mobile'];
    }
    $spreadsheet = new Spreadsheet();
    $spreadsheet->getProperties()->setCreator("Admin")
      ->setLastModifiedBy("Admin")
      ->setTitle("Users of points")
      ->setSubject("Users")
      ->setDescription("Users of points which you get")
      ->setKeywords("Users")
      ->setCategory("Users of points");
    $spreadsheet->setActiveSheetIndex(0);
    $spreadsheet->getActiveSheet()->setTitle('用户积分列表');
    $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(25); //首行高度
    // 设置表格宽度
    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(25);
    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(25);
    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
    // 列名表头文字加粗
    $spreadsheet->getActiveSheet()->getStyle('A1:' . 'E1')->getFont()->setBold(true);
    // 列表头文字居中
    $spreadsheet->getActiveSheet()->getStyle('A1:' . 'E1')->getAlignment()
      ->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // 列名赋值
    $spreadsheet->getActiveSheet()->setCellValue('A1', '序号');
    $spreadsheet->getActiveSheet()->setCellValue('B1', '用户ID');
    $spreadsheet->getActiveSheet()->setCellValue('C1', '积分');
    $spreadsheet->getActiveSheet()->setCellValue('D1', '时间');
    $spreadsheet->getActiveSheet()->setCellValue('E1', '手机号');
    // 数据起始行
    $row_num = 2;
    // 向每行单元格插入数据
    foreach ($user_points as $key => $value) {
      // 设置所有垂直居中
      $spreadsheet->getActiveSheet()->getStyle('A' . $row_num . ':' . 'D' . $row_num)->getAlignment()
        ->setHorizontal(Alignment::HORIZONTAL_CENTER);
      // 设置单元格数值
      $spreadsheet->getActiveSheet()->setCellValue('A' . $row_num, $key + 1);
      $spreadsheet->getActiveSheet()->setCellValue('B' . $row_num, $value['user_id']);
      $spreadsheet->getActiveSheet()->setCellValue('C' . $row_num, $value['point']);
      $spreadsheet->getActiveSheet()->setCellValue('D' . $row_num, $value['year'] . '/' . $value['month']);
      $spreadsheet->getActiveSheet()->setCellValue('E' . $row_num, $user[$value['user_id']]);
      $row_num++;
    }
    $objWriter = IOFactory::createWriter($spreadsheet, 'Xls');
    $outputFileName = '侃侃大学' . $month . '月用户积分表.xls';
    //输出到浏览器
    ob_end_clean();
    header('Content-Type: application/vnd.oasis.opendocument.spreadsheet');
    header('Content-Disposition: attachment;filename="' . $outputFileName . '"');
    header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0
    $objWriter->save('php://output');
    exit;
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.06.05
  * ps：设置用户星级
  * */
  public function setRank()
  {
    $user_id = $this->request->getData('user_id');
    $rank = $this->request->getData('rank');
    if (empty($user_id)) {
      $this->G->error("user_id_error");
      return null;
    }
    $this->loadModel('UserInfos');
    $this->UserInfos->query()
      ->update()
      ->set(['rank' => $rank])
      ->where(['user_id' => $user_id])
      ->execute();
    $this->loadComponent('UserCache');
    $this->UserCache->deleteUserCache($user_id);
    $this->UserCache->deleteCache();
    $this->G->success('Success');
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：获取用户组
  * */
  public function getAro()
  {
    $this->loadModel('UserAros');
    $user_aros = $this->UserAros->find()
      ->select(['id', 'alias'])
      ->order(['id asc'])
      ->toArray();
    $this->G->success("Success", $user_aros);
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：取消大咖
  * */
  public function cancelExpert()
  {
    $ids = $this->request->getData("ids");
    if (empty($ids) || !is_array($ids)) {
      $this->G->error("ids_error");
      return null;
    }
    $this->loadComponent('SysconfigCache');
    $sys = $this->SysconfigCache->getVal('expert');
    $this->loadComponent('CategoryContentCache');
    foreach ($ids as $k => $v) {
      $this->CategoryContentCache->delete('users', $v, $sys);
    }
    $this->loadModel('GoodsContents');
    $this->GoodsContents->deleteAll(['content_type' => 'users', 'content_id in' => $ids]);
    $this->G->success("Success");
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：获取大咖列表
  * */
  public function getExpert()
  {
    $this->loadComponent('SysconfigCache');
    $sys = $this->SysconfigCache->getVal('expert');
    $this->loadModel('CategoryContents');
    $where = [];
    $category_content = $this->CategoryContents->find()
      ->select(['content_id'])
      ->where(['category_id' => $sys, 'content_type' => 'users'])
      ->order(['order_num desc'])
      ->toArray();
    $where['id in'] = empty($category_content) ? [0] : array_column($category_content, 'content_id');
    $users = $this->Users->find()
      ->select(['id'])
      ->where($where)
      ->toArray();
    $list = [];
    if (!empty($users)) {
      $this->loadComponent('UserCache');
      foreach ($users as $k => $v) {
        $user = $this->UserCache->getVal($v['id'], null, 'UserInfos');
        if (empty($user)) {
          continue;
        }
        $user['avatar'] = $this->G->getImgUrl($user['avatar']);
        $list[] = $user;
      }
    }
    $this->G->success("Success", $list);
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：设置大咖
  * */
  public function setExpert()
  {
    $ids = $this->request->getData("ids");
    $price = $this->request->getData("price");
    $deal_id = $this->request->getData("deal_id");
    if (empty($ids) || !is_array($ids)) {
      $this->G->error("ids_error");
      return null;
    }
    if (!is_numeric($price)) {
      $this->G->error("price_error");
      return null;
    }
    $this->loadComponent('SysconfigCache');
    $sys = $this->SysconfigCache->getVal('expert');
    $this->loadModel('GoodsContents');
    $this->GoodsContents->deleteAll(['content_type' => 'users', 'content_id in' => $ids]);
    $this->loadComponent('CategoryContentCache');
    $this->loadModel('Goods');
    $good = $this->Goods->newEntity();
    $good->title = '问大咖费用';
    $good->img = 'expert.png';
    $good->price = $price * 100;
    $good->memo = json_encode($ids);
    $good->status = 'enabled';
    $good->max_buy = 1;
    $good->min_buy = 1;
    $good->pre_num = 0;
    $good_save = $this->Goods->save($good);
    $this->loadModel('GoodsInstocks');
    $good_instock = $this->GoodsInstocks->newEntity();
    $good_instock->good_id = $good_save->id;
    $good_instock->price = $price * 100;
    $good_instock->num = 999999;
    $good_instock->add_time = time();
    $this->GoodsInstocks->save($good_instock);
    if (!empty($deal_id)) {
      $this->loadModel('DealGoods');
      $deal_good = $this->DealGoods->newEntity();
      $deal_good->deal_type = 'deal_points';
      $deal_good->deal_id = $deal_id;
      $deal_good->good_id = $good_save->id;
      $this->DealGoods->save($deal_good);
      $this->loadComponent('DealGoodCache');
      $this->DealGoodCache->deleteCache($good_save->id);
    }
    $this->loadComponent('GoodCache');
    foreach ($ids as $k => $v) {
      $this->CategoryContentCache->add('users', $v, $sys);
      $goods_content = $this->GoodsContents->newEntity();
      $goods_content->content_type = 'users';
      $goods_content->content_id = $v;
      $goods_content->good_id = $good_save->id;
      $this->GoodsContents->save($goods_content);
      $this->GoodCache->deleteList('users', $v);
    }
    $this->loadComponent('UserCache');
    $this->UserCache->deleteCache();
    $this->G->success("Success");
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：-为用户设置积分
  * */
  public function setPoint()
  {
    $user_id = $this->request->getData("user_id");
    $deals = $this->request->getData("deals");
    if (empty($user_id)) {
      $this->G->error("user_id_error");
      return null;
    }
    if (empty($deals)) {
      $this->G->error("deals_error");
      return null;
    }
    $this->loadModel('DealPoints');
    $this->loadModel('DealUsers');
    $this->loadComponent('DealUserCache');
    $this->DealUserCache->deleteCache($user_id);
    foreach ($deals as $k => $v) {
      $deal_users = $this->DealUsers->find()
        ->select(['id'])
        ->where(['deal_id' => $v['id'], 'user_id' => $user_id])
        ->first();
      if (!empty($deal_users)) {
        $this->DealUsers->query()
          ->update()
          ->set(['num' => $v['num']])
          ->where(['id' => $deal_users['id']])
          ->execute();
        continue;
      }
      $deal_point = $this->DealPoints->findById($v['id'])->first();
      $deal_user = $this->DealUsers->newEntity();
      $deal_user->deal_type = 'deal_points';
      $deal_user->deal_id = $v['id'];
      $deal_user->user_id = $user_id;
      $deal_user->num = $v['num'];
      $deal_user->begin_time = $deal_point['begin_time'];
      $deal_user->end_time = $deal_point['end_time'];
      $deal_user->status = 'enabled';
      $this->DealUsers->save($deal_user);
    }
    $this->G->success("Success");
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：获取用户信息
  * */
  public function getInfo()
  {
    $user_id = $this->request->getData("user_id");
    $this->loadModel('UserInfos');
    $this->loadModel('UserGroups');
    $this->loadModel('DealUsers');
    $this->loadModel('DealPoints');
    $this->loadModel('UserAros');
    $user = $this->Users->findById($user_id)->first();
    $user_info = $this->UserInfos->findByUserId($user_id)->first();
    $user_group = $this->UserGroups->findByUserId($user_id)->first();
    $user_aro = $this->UserAros->findById($user_group['user_aro_id'])->first();
    $deal_users = $this->DealUsers->findByUserId($user_id)->toArray();
    $deal_points = $this->DealPoints->find()->toArray();
    $deal_point = [];
    if (!empty($deal_points)) {
      foreach ($deal_points as $k => $v) {
        $deal_point[$v['id']] = $v['title'];
      }
    }
    $data = [];
    $user['login_time'] = date('Y-m-d H:i:s', $user['login_time']);
    $data['users'] = $user;
    $user_group['aro_name'] = $user_aro['alias'];
    $user_group['begin_time'] = date('Y-m-d H:i:s', $user_group['begin_time']);
    $user_group['end_time'] = date('Y-m-d H:i:s', $user_group['end_time']);
    $data['user_groups'] = $user_group;
    $user_info['avatar'] = $this->G->getImgUrl($user_info['avatar']);
    $data['user_infos'] = $user_info;
    $data['user_points'] = [];
    if (!empty($deal_users)) {
      foreach ($deal_users as $k => $v) {
        $data['user_points'][] = [
          'point_id' => $v['deal_id'],
          'point_name' => empty($deal_point[$v['deal_id']]) ? '' : $deal_point[$v['deal_id']],
          'point' => $v['num'],
        ];
      }
    }
    $this->G->success("Success", $data);
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：设置可用/不可用
  * */
  public function setEnabled()
  {
    if (!$this->request->is("post")) {
      $this->G->error("post_error");
      return null;
    }
    $ids = $this->request->getData("ids");
    $enabled = $this->request->getData("enabled");
    if (empty($ids) || !is_array($ids)) {
      $this->G->error("ids_error");
      return null;
    }
    if ($enabled != 0 && $enabled != 1) {
      $this->G->error("enabled_error");
      return null;
    }
    $this->Users->query()
      ->update()
      ->set(['enabled' => $enabled])
      ->where(['id in' => $ids])
      ->execute();
    $this->loadComponent('UserCache');
    foreach ($ids as $k => $v) {
      $this->UserCache->deleteUserCache($v);
    }
    $this->G->success("Success");
  }

  /*
  * create by phpstorm
  * author：Ming
  * date：2020.04.30
  * ps：设置为用户为vip
  * */
  public function setVip()
  {
    $data = $this->request->getData();
    $this->loadModel('UserInfos');
    $this->UserInfos->query()
      ->update()
      ->set(['id_card' => $data['id_card']])
      ->where(['user_id' => $data['user_id']])
      ->execute();
    $this->loadComponent('UserCache');
    $this->UserCache->deleteUserCache($data['user_id']);
    $this->UserCache->deleteCache();
    $this->G->success('Success');
  }

  /*
 * create by phpstorm
 * author：Ming
 * date：2020.04.30
 * ps：获取用户列表
 * */
  public function getLists()
  {
    $data = $this->request->getData();
    $data['page'] = empty($data['page']) ? 1 : $data['page'];
    $where = [];
    $where1 = [];
    if (!empty($data['id'])) {
      $where['id'] = $data['id'];
    }
    if (!empty($data['mobile'])) {
      $where['mobile like '] = '%' . $data['mobile'] . '%';
    }
    if (!empty($data['nickname'])) {
      $where1['nickname like '] = '%' . $data['nickname'] . '%';
    }
    if (isset($data['enabled']) && is_numeric($data['enabled'])) {
      $where['enabled'] = $data['enabled'];
    }
    $this->loadModel('UserGroups');
    $this->loadModel('UserAros');
    $this->loadModel('UserInfos');
    $user_aros = $this->UserAros->find()
      ->select(['id', 'alias'])
      ->toArray();
    $user_aro = [];
    foreach ($user_aros as $k => $v) {
      $user_aro[$v['id']] = $v['alias'];
    }
    $aro_where = [];
    if (!empty($data['aro_id'])) {
      $aro_where['user_aro_id'] = $data['aro_id'];
    }
    $user_groups = $this->UserGroups->find()
      ->where($aro_where)
      ->toArray();
    $re = [];
    $re['pageTotal'] = 1;
    $re['list'] = [];
    if (!empty($user_groups)) {
      $user_group = [];
      foreach ($user_groups as $k1 => $v1) {
        $user_group[$v1['user_id']] = [
          'aro_alias' => $user_aro[$v1['user_aro_id']],
          'begin_time' => empty($v1['begin_time']) ? '' : date('Y-m-d H:i:s', $v1['begin_time']),
          'end_time' => empty($v1['end_time']) ? '' : date('Y-m-d H:i:s', $v1['end_time']),
        ];
      }
      $user_infos = $this->UserInfos->find()
        ->where(['user_id in' => array_column($user_groups, 'user_id'), $where1])
        ->toArray();
      if (!empty($user_infos)) {
        $user_info = [];
        foreach ($user_infos as $k2 => $v2) {
          $user_info[$v2['user_id']] = [
            'nickname' => $v2['nickname'],
            'id_card' => $v2['id_card']
          ];
        }
        $where['id in '] = array_column($user_infos, 'user_id');
        $users = $this->Users->find()
          ->select(['id', 'mobile', 'enabled'])
          ->where($where)
          ->order(['id desc'])
          ->limit($this->_page_num)
          ->page($data['page']);
        $count = $users->count();
        $re['pageTotal'] = ceil($count / $this->_page_num);
        $users = $users->toArray();
        if (!empty($users)) {
          $this->loadModel('Companies');
          $companies = $this->Companies->find()
            ->where(['create_user in' => array_column($users, 'id')])
            ->toArray();
          $company = [];
          foreach ($companies as $k1 => $v1) {
            $company[$v1['create_user']] = $v1['title'];
          }
          foreach ($users as $k3 => $v3) {
            $re['list'][] = [
              'id' => $v3['id'],
              'mobile' => $v3['mobile'],
              'enabled' => $v3['enabled'],
              'company_title' => empty($company[$v3['id']]) ? '' : $company[$v3['id']],
              'username user' => $v3['username'],
              'nickname' => $user_info[$v3['id']]['nickname'],
              'aro_alias' => $user_group[$v3['id']]['aro_alias'],
              'begin_time' => $user_group[$v3['id']]['begin_time'],
              'end_time' => $user_group[$v3['id']]['end_time'],
              'id_card' => $user_info[$v3['id']]['id_card'],
            ];
          }
        }
      }
    }
    $this->G->success('Success', $re);
  }

  /*
 * create by phpstorm
 * author：DJX
 * date：2020.11.26
 * ps：获取用户列表
 * */
  public function getList()
  {
    $data = $this->request->getData();
    $data['page'] = empty($data['page']) ? 1 : $data['page'];
    $where = [];
    $where1 = [];
    if (!empty($data['id'])) {
      $where['id'] = $data['id'];
    }
    if (!empty($data['mobile'])) {
      $where['mobile like '] = '%' . $data['mobile'] . '%';
    }
    if (!empty($data['nickname'])) {
      $where1['nickname like '] = '%' . $data['nickname'] . '%';
      $where1['username like '] = '%' . $data['nickname'] . '%';
    }
    if (isset($data['enabled']) && is_numeric($data['enabled'])) {
      $where['enabled'] = $data['enabled'];
    }

    $re = [];
    $re['pageTotal'] = 1;
    $re['list'] = [];

    $users = $this->Users->find()
      ->select(['id', 'username', 'mobile', 'enabled'])
      ->where($where)
      ->order(['id desc'])
      ->limit($this->_page_num)
      ->page($data['page']);
    $count = $users->count();
    $re['pageTotal'] = ceil($count / $this->_page_num);
    // 通过UserId获取关联企业
    $users = $users->toArray();
    $this->loadModel('UserCompanies');
    $uc_lists = $this->UserCompanies->find()
      ->select(['company_id', 'user_id'])
      ->where(['user_id in' => array_column($users, 'id')])
      ->toArray();

    $this->loadModel('Companies');
    $company = $this->Companies->find()
      ->select(['id', 'title'])
      ->where(['id in' => array_column($uc_lists, 'company_id')])
      ->toArray();

    $companyToTitle = array_column($company, 'title', 'id');
    $uc = array_column($uc_lists, 'company_id', 'user_id');

    foreach ($users as $k => $v) {

      $cid = $uc[$v['id']];
      $ctitle = empty($companyToTitle[$cid]) ? '' : $companyToTitle[$cid];

      $re['list'][] = [
        'id' => $v['id'],
        'username' => $v['username'],
        'mobile' => $v['mobile'],
        'enabled' => $v['enabled'],
        'company_title' => $ctitle
      ];
    }
    $this->G->success('Success', $re);
  }

  /*
 * create by phpstorm
 * author：Ming
 * date：2020.04.30
 * ps：获取用户列表
 * */
  public function getList1()
  {
    $data = $this->request->getData();
    $where = [];
    if (!empty($data['id'])) {
      $where['id'] = $data['id'];
    }
    if (!empty($data['mobile'])) {
      $where['mobile like '] = '%' . $data['mobile'] . '%';
    }
    if (is_numeric($data['enabled'])) {
      $where['enabled'] = $data['enabled'];
    }
    $users = $this->Users->find()
      ->select(['id', 'mobile', 'enabled'])
      ->where($where)
      ->order(['id desc'])
      ->limit($this->_page_num)
      ->page($data['page']);
    $re = [];
    $count = $users->count();
    $re['pageTotal'] = ceil($count / $this->_page_num);
    $users = $users->toArray();
    $re['list'] = [];
    if (!empty($users)) {
      $this->loadModel('UserGroups');
      $this->loadModel('UserAros');
      $this->loadModel('UserInfos');
      $user_aros = $this->UserAros->find()
        ->select(['id', 'alias'])
        ->toArray();
      $user_aro = [];
      foreach ($user_aros as $k => $v) {
        $user_aro[$v['id']] = $v['alias'];
      }
      $aro_where = [];
      $aro_where['user_id in'] = array_column($users, 'id');
      if (!empty($data['aro_id'])) {
        $aro_where['user_aro_id'] = $data['aro_id'];
      }
      $user_groups = $this->UserGroups->find()
        ->where($aro_where)
        ->toArray();
      $user_group = [];
      if (!empty($user_groups)) {
        foreach ($user_groups as $k1 => $v1) {
          $user_group[$v1['user_id']] = [
            'aro_alias' => $user_aro[$v1['user_aro_id']],
            'begin_time' => date('Y-m-d H:i:s', $v1['begin_time']),
            'end_time' => date('Y-m-d H:i:s', $v1['end_time']),
          ];
        }
        $user_infos = $this->UserInfos->find()
          ->where(['user_id in' => array_column($user_groups, 'user_id')])
          ->toArray();
        $user = [];
        foreach ($users as $k2 => $v2) {
          $user[$v2['id']] = [
            'id' => $v2['id'],
            'mobile' => $v2['mobile'],
            'enabled' => $v2['enabled'],
          ];
        }
        foreach ($user_infos as $k3 => $v3) {
          $re['list'][] = [
            'id' => $user[$v3['user_id']]['id'],
            'mobile' => $user[$v3['user_id']]['mobile'],
            'enabled' => $user[$v3['user_id']]['enabled'],
            'nickname' => $v3['nickname'],
            'aro_alias' => $user_group[$v3['user_id']]['aro_alias'],
            'begin_time' => $user_group[$v3['user_id']]['begin_time'],
            'end_time' => $user_group[$v3['user_id']]['end_time'],
          ];
        }
      }
    }
    $this->G->success('Success', $re);
  }

}

?>
